package crm.control;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import crm.dbcoon.DBConnect;
import crm.model.*;
import crm.util.Pagination;



public class ProductControl {
	public static Pagination pageInstance1 = null;
	public static Pagination pageInstance2 = null;
	public static Pagination pageInstance3 = null;
	/**
	 * 添加系统商品
	 */
	public static Boolean addProductSystem(Product_system productSystem) {
		DBConnect dbc=new DBConnect();
		String sql="insert into product_system (create_time,description,develope_department,developer,price,system_name,system_number)values(?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try {
			int index =1;
			dbc.setString(index++,productSystem.getCreate_time());
			dbc.setString(index++,productSystem.getDescription());
			dbc.setString(index++,productSystem.getDevelope_department());
			dbc.setString(index++,productSystem.getDeveloper());
			dbc.setString(index++,productSystem.getPrice());
			dbc.setString(index++,productSystem.getSystem_name());
			dbc.setString(index++,productSystem.getSystem_number());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 添加模块商品
	 */
	public static Boolean addProductModel(Product_model productModel){
		DBConnect dbc = new DBConnect();
		String sql = "insert into product_model (model_name,model_number,price,description,system_product_id,create_time,develope_depart,developer) values(?,?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,productModel.getModel_name());
			dbc.setString(2,productModel.getModel_number());
			dbc.setString(3,productModel.getPrice());
			dbc.setString(4,productModel.getDescription());
			dbc.setInt(5,productModel.getSystem_product_id());
			dbc.setString(6,productModel.getCreate_time());
			dbc.setString(7,productModel.getDevelope_depart());
			dbc.setString(8,productModel.getDeveloper());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
			// TODO: handle exception
		}finally{
			dbc.close();
		}
	}
	/**
	 * 添加服务商品
	 */
	public static Boolean addProductService(Product_service productService) {
		DBConnect dbc = new DBConnect();
		String sql="insert into product_service(description,create_time,price,service_name,service_number)values(?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,productService.getDescription());
			dbc.setString(2,productService.getCreate_time());
			dbc.setString(3,productService.getPrice());
			dbc.setString(4,productService.getService_name());
			dbc.setString(5,productService.getService_number());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{dbc.close();}
	}
	/**
	 * 删除系统商品
	 */
	public static Boolean deleteProductSystem(int id) {
		Product_system productSystem=new Product_system();
		DBConnect dbc=new DBConnect();
		String sql="delete from product_system where id="+id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 删除服务商品
	 */
	public static Boolean deleteProductService(int id) {
		DBConnect dbc=new DBConnect();
		Product_service productService=new Product_service();
		String sql="delete from product_service where id="+id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
			// TODO: handle exception
		}finally{
			dbc.close();
		}
	}
	/**
	 * 删除模块商品
	 */
	public static Boolean deleteProductModel(int id){
		DBConnect dbc=new DBConnect();
		Product_model productModel=new Product_model();
		String sql="delete from product_model where id="+id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 修改系统产品
	 */
	public static Boolean updateProductSystem(int id) {
		DBConnect dbc=new DBConnect();
		Product_system productSystem=new Product_system();
//		int id= productSystem.getId();
		String sql="update product_system set create_time=?,description=?,develope_department=?,developer=?,price=?,system_name=?,system_number=? where id="+productSystem.getId();
		dbc.prepareStatement(sql);
		try {
			int index=1;
			dbc.setString(index++,productSystem.getCreate_time());
			dbc.setString(index++,productSystem.getDescription());
			dbc.setString(index++,productSystem.getDevelope_department());
			dbc.setString(index++,productSystem.getDeveloper());
			dbc.setString(index++,productSystem.getPrice());
			dbc.setString(index++,productSystem.getSystem_name());
			dbc.setString(index++,productSystem.getSystem_number());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 修改服务商品
	 */
	public static Boolean updateProductService(int id) {
		Product_service productService=new Product_service();
		DBConnect dbc=new DBConnect();
		String sql="update product_service set service_name=?,service_number=?,description=?,create_time=?,price=? where id="+productService.getId();
		dbc.prepareStatement(sql);
		try { 
			int index=1;
			dbc.setString(index++,productService.getService_name());
			dbc.setString(index++,productService.getService_number());
			dbc.setString(index++,productService.getDescription());
			dbc.setString(index++,productService.getCreate_time());
			dbc.setString(index++,productService.getPrice());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 修改模块商品
	 */
	public static Boolean updateProductModel(int id){
		DBConnect dbc=new DBConnect();
		Product_model productModel=new Product_model();
		String sql= "update product_model set model_name= ? , model_number = ? , price= ? , description= ? , system_product_id= ? , create_time= ? , develope_depart= ? , developer= ? where id=2";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,productModel.getModel_name());
			dbc.setString(2,productModel.getModel_number());
			dbc.setString(3,productModel.getPrice());
			dbc.setString(4,productModel.getDescription());
			dbc.setInt(5,productModel.getSystem_product_id());
			dbc.setString(6,productModel.getCreate_time());
			dbc.setString(7,productModel.getDevelope_depart());
			dbc.setString(8,productModel.getDeveloper());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 获得系统产品
	 */
	public static Product_system getProductSystemById(int id) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_system where id="+id;
		dbc.prepareStatement(sql);
		try {
			Product_system productSystem=new Product_system();
			ResultSet rs=dbc.executeQuery();
			if (rs.next()) {
				productSystem.setCreate_time(rs.getString("create_time"));
				productSystem.setDescription(rs.getString("description"));
				productSystem.setDevelope_department(rs.getString("develope_department"));
				productSystem.setDeveloper(rs.getString("developer"));
				productSystem.setPrice(rs.getString("price"));
				productSystem.setSystem_name(rs.getString("system_name"));
				productSystem.setSystem_number(rs.getString("system_number"));
				return productSystem;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			// TODO: handle exception
		}finally{dbc.close();}
		return null;
	}
	/**
	 * 获得服务产品
	 */
	public static Product_service getProductServiceById(int id) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_service where id="+id;
		dbc.prepareStatement(sql);
		try {
			Product_service productService=new Product_service();
			ResultSet rs=dbc.executeQuery();
			if (rs.next()) {
				productService.setCreate_time(rs.getString("create_time"));
				productService.setDescription(rs.getString("description"));
				productService.setPrice(rs.getString("price"));
				productService.setService_name(rs.getString("service_name"));
				productService.setService_number(rs.getString("service_number"));
				return productService;
			}
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
			return null;
		}finally{
			dbc.close();
		}
		return null;
	}
	/**
	 * 获得模块产品
	 */
	public static Product_model getProductModelById(int id) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_model where id=?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,id);
			Product_model productModel=new Product_model();
			ResultSet rs= dbc.executeQuery();
			if (rs.next()) {
				productModel.setCreate_time(rs.getString("create_time"));
				productModel.setDescription(rs.getString("description"));
				productModel.setDevelope_depart(rs.getString("develope_depart"));
				productModel.setDeveloper(rs.getString("developer"));
				productModel.setModel_name(rs.getString("model_name"));
				productModel.setModel_number(rs.getString("model_number"));
				productModel.setPrice(rs.getString("price"));
				productModel.setSystem_product_id(rs.getInt("system_product_id"));
				return productModel;
			}
		} catch (Exception e) {
		     e.printStackTrace();
			// TODO: handle exception
		     return null;
		}finally{dbc.close();}
		return null;
	}
	/**
	 * 查找模块商品
	 */
	public static Collection searchModel(String model_number,String model_name,String create_time,int rowsPerPage,int page) {
		if (model_number==null||model_number.equals("")) {
			model_number="%";
		}
		if (model_name==null||model_name.equals("")) {
			model_name="%";
		}
		if (create_time==null||create_time.equals("")) {
			create_time="%";
		}
		String sql="select * from product_model where model_number like'"+model_number+"' and model_name like'"+model_name+"' and create_time like'"+create_time+"'";
		System.out.println("aaaa"+sql);
		pageInstance2=new modelControlPage(sql, rowsPerPage);
		try {
			return pageInstance2.getPage(page);
		} catch (Exception e) {
			// TODO: handle exception
			return null;
		}
	}
	/**
	 * 判断系统名称是否存在
	 */
	public static Boolean systemNameIsExist(String system_name) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_system where system_name='"+system_name+"'";
		System.out.println(sql);
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		return false;
	}
	/**
	 * 判断系统编号是否存在
	 */
	public static Boolean systemNumberIsExist(String system_number) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_system where system_number='"+system_number+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		return false;
	}
	/**
	 * 判断模块编号是否存在
	 */
	public static Boolean modelNumberIsExist(String model_number) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_model where model_number='"+model_number+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		return false;
	}
	/**
	 * 判断模块名称是否存在
	 */
	public static Boolean modelNameIsExist(String model_name) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_model where model_name='"+model_name+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		return false;
	}
	/**
	 * 判断服务编号是否存在
	 */
	public static Boolean serviceNumberIsExist(String service_number) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_service where service_number='"+service_number+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		return false;
	}
	/**
	 * 判断服务名称是否存在
	 */
	public static Boolean serviceNameIsExist(String service_name) {
		DBConnect dbc=new DBConnect();
		String sql="select * from product_service where service_name='"+service_name+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}return false;
	}
	/**
	 * getModelByRs  获取系统结果集
	 */	
	public static void getSystemByRs(Product_system productSystem,ResultSet rs) {
		try {
			productSystem.setId(rs.getInt("id"));
			productSystem.setCreate_time(rs.getString("create_time"));
			productSystem.setDescription(rs.getString("description"));
			productSystem.setDevelope_department(rs.getString("develope_department"));
			productSystem.setDeveloper(rs.getString("developer"));
			productSystem.setPrice(rs.getString("price"));
			productSystem.setSystem_name(rs.getString("system_name"));
			productSystem.setSystem_number(rs.getString("system_number"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	/**
	 * getModelByRs  获取模块结果集
	 */		
	public static void  getModelByRs(Product_model productModel,ResultSet rs) {
		try {
			productModel.setId(rs.getInt("id"));
			productModel.setCreate_time(rs.getString("create_time"));
			productModel.setDescription(rs.getString("description"));
			productModel.setDevelope_depart(rs.getString("develope_depart"));
			productModel.setDeveloper(rs.getString("developer"));
			productModel.setModel_name(rs.getString("model_name"));
			productModel.setModel_number(rs.getString("model_number"));
			productModel.setPrice(rs.getString("price"));
			productModel.setSystem_product_id(rs.getInt("system_product_id"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getModelByRs出错");
		}
	}
	/**
	 * getServiceByRs  获取服务结果集
	 */
	public static void getServiceByRs(Product_service productService,ResultSet rs) {
		try {
			productService.setId(rs.getInt("id"));
			productService.setCreate_time(rs.getString("create_time"));
			productService.setDescription(rs.getString("description"));
			productService.setPrice(rs.getString("price"));
			productService.setService_name(rs.getString("service_name"));
			productService.setService_number(rs.getString("service_number"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * @param pages
	 * @param rowsPerpage
	 * @param create_time
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public static Collection getSystem(int pages,int rowsPerpage,String create_time)
	throws SQLException,Exception{
		String sql="select * from product_system order by "+create_time+" desc";
		pageInstance1 = new systemControlPage(sql, rowsPerpage);
		return pageInstance1.getPage(pages);
	}
	/**
	 * 
	 * @param pages
	 * @param rowsPerpage
	 * @param create_time
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public static Collection getModel(int pages,int rowsPerpage,String create_time)
	throws SQLException,Exception{
		String sql="select * from product_model order by "+create_time+" desc";
		pageInstance2 = new modelControlPage(sql, rowsPerpage);
		return pageInstance2.getPage(pages);
	}
	/**
	 * 
	 * @param pages
	 * @param rowsPerpage
	 * @param create_time
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public static Collection getService(int pages,int rowsPerpage,String create_time)
	throws SQLException,Exception{
		String sql="select * from product_service order by "+create_time+" desc";
		pageInstance3 = new serviceControlPage(sql, rowsPerpage);
		return pageInstance3.getPage(pages);
	}
}

/**
 * @function模块分页显示
 */
class modelControlPage extends Pagination{
	public modelControlPage (String sql,int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	protected Collection<Product_model> packResultSet(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		Collection<Product_model> plist=new ArrayList<Product_model>();
		try {
			while(rs.next()){
				Product_model productModel=new Product_model();
				ProductControl.getModelByRs(productModel, rs);
				plist.add(productModel);
				productModel=null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return plist;
	}	
}
/**
 * 
 * @function 系统分页显示
 *
 */
class systemControlPage extends Pagination{
	public systemControlPage(String sql,int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	protected Collection<Product_system> packResultSet(ResultSet rs) throws SQLException {
		Collection<Product_system> plist=new ArrayList<Product_system>();
		try {
			while (rs.next()) {
				Product_system productSystem=new Product_system();
				ProductControl.getSystemByRs(productSystem, rs);
				plist.add(productSystem);
				productSystem=null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return plist;
	}
}
/**
 * 
 * 服务分页显示
 *
 */

class serviceControlPage extends Pagination{
	public serviceControlPage(String sql,int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	protected Collection<Product_service> packResultSet(ResultSet rs) throws SQLException{
		Collection<Product_service> plist=new ArrayList<Product_service>();
		try {
			while (rs.next()) {
				Product_service productService=new Product_service();
				ProductControl.getServiceByRs(productService, rs);
				plist.add(productService);
				productService=null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return plist;
	}
}
